﻿using MyORM.core;
using MyORM.mapper;
using MyUtils;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace MyORM
{
    class DefaultSqlSession : ISqlSesstion
    {
        private string str;

        public DefaultSqlSession(string str)
        {
            this.str = str;
        }

        public bool SaveOrUpdate(object obj)
        {
            if(obj == null)
                return false;
            TableStruct ts = new TableStruct(obj);
            Dictionary<int, object> dict = ts.BuildDict("insertorupdate"); ;
            if (dict != null && dict.Count > 0)
            {
                string sql = dict[0].ToString();
                dict.Remove(0);
                object[] array = new object[dict.Count];
                dict.Values.CopyTo(array, 0);
                if (sql.EndsWith(";SELECT @ide=@@IDENTITY"))
                {
                    object result;
                    if (ExecuteWithIdentity(sql, out result, array))
                    {
                        ts.updateId(Convert.ToInt32(result));
                        return true;
                    }
                    return false;
                }
                return Execute(sql, array);
            }
            return false;
        }

        private bool ExecuteWithIdentity(string sql, out object pkValue, object[] objs)
        {
            int result = 0;
            try
            {
                using (SqlConnection conn = new SqlConnection(str))
                {
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = sql;
                    if (objs != null && objs.Length > 0)
                    {
                        for (int i = 0; i < objs.Length; i++)
                        {
                            cmd.Parameters.AddWithValue("@" + i, objs[i]);
                        }
                    }
                    SqlParameter para = new SqlParameter("@ide", SqlDbType.Int);
                    para.Direction = ParameterDirection.Output;
                    cmd.Parameters.Add(para);
                    result = cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    if (result > 0)
                        pkValue = para.Value;
                    else
                        pkValue = null;
                }
            }
            catch (SqlException e)
            {
                pkValue = null;
                LogHelper.Error("执行失败：", e);
            }
            return result > 0;
        }

        public bool Execute(string sql, params object[] objs)
        {
            int result = 0;
            try
            {
                using (SqlConnection conn = new SqlConnection(str))
                {
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = sql;
                    if (objs != null && objs.Length > 0)
                    {
                        for (int i = 0; i < objs.Length; i++)
                        {
                            cmd.Parameters.AddWithValue("@" + i, objs[i]);
                        }
                    }
                    result = cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    conn.Close();
                }
            }
            catch (SqlException e)
            {
                LogHelper.Error("执行失败：", e);
            }
            return result > 0;
        }

        public T Single<T>(string sql, params object[] objs)
        {
            List<T> tt = Query<T>(sql, objs);
            if (tt != null && tt.Count == 1)
                return tt[0];
            return default(T);
        }

        public DataTable QueryTable(string sql, params object[] objs)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(str))
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                if (objs != null && objs.Length > 0)
                {
                    for (int i = 0; i < objs.Length; i++)
                    {
                        cmd.Parameters.AddWithValue("@" + i, objs[i]);
                    }
                }
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                sda.Fill(dt);
                sda.Dispose();
                cmd.Dispose();
                conn.Close();
            }
            return dt;
        }

        public List<T> Query<T>(string sql, params object[] objs)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(str))
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                if (objs != null && objs.Length > 0)
                {
                    for (int i = 0; i < objs.Length; i++)
                    {
                        cmd.Parameters.AddWithValue("@" + i, objs[i]);
                    }
                }
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                sda.Fill(dt);
                sda.Dispose();
                cmd.Dispose();
                conn.Close();
            }
            List<T> tt = new List<T>();
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    T t = Activator.CreateInstance<T>();
                    tt.Add((T)ObjectUtils.copyValue(t, dr));
                }
            }
            return tt;
        }


        public bool Run(string procName, List<SqlParameter> paras)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(str))
                {
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = procName;
                    if (paras != null && paras.Count > 0)
                    {
                        foreach (SqlParameter para in paras)
                        {
                            cmd.Parameters.Add(para);
                        }
                    }
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    conn.Close();
                }
                return true;
            }
            catch (SqlException ex)
            {
                LogHelper.Error("执行存储过程失败", ex);
                return false;
            }
        }


        public object GetSingle(string sql, params object[] objs)
        {
            object obj = null;
            using (SqlConnection conn = new SqlConnection(str))
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                if (objs != null && objs.Length > 0)
                {
                    for (int i = 0; i < objs.Length; i++)
                    {
                        cmd.Parameters.AddWithValue("@" + i, objs[i]);
                    }
                }
                obj = cmd.ExecuteScalar();
                if (obj is DBNull)
                    obj = null;
                cmd.Dispose();
                conn.Close();
            }
            return obj;
        }


        public SqlTransaction beginTransaction()
        {
            SqlConnection conn = new SqlConnection(str);
            conn.Open();
            return conn.BeginTransaction();
        }

        public bool SaveOrUpdate(SqlTransaction tran, object obj)
        {
            TableStruct ts = new TableStruct(obj);
            Dictionary<int, object> dict = ts.BuildDict("insertorupdate");
            if (dict != null && dict.Count > 0)
            {
                string sql = dict[0].ToString();
                dict.Remove(0);
                object[] array = new object[dict.Count];
                dict.Values.CopyTo(array, 0);
                if (sql.EndsWith(";SELECT @ide=@@IDENTITY"))
                {
                    object result;
                    if (ExecuteWithIdentity(tran, sql, out result, array))
                    {
                        ts.updateId(Convert.ToInt32(result));
                        return true;
                    }
                    return false;
                }
                return Execute(tran, sql, array);
            }
            return false;
        }

        private bool ExecuteWithIdentity(SqlTransaction tran, string sql, out object pkValue, object[] objs)
        {
            int result = 0;
            SqlConnection conn = tran.Connection;
            SqlCommand cmd = conn.CreateCommand();
            cmd.Transaction = tran;
            cmd.CommandText = sql;
            if (objs != null && objs.Length > 0)
            {
                for (int i = 0; i < objs.Length; i++)
                {
                    cmd.Parameters.AddWithValue("@" + i, objs[i]);
                }
            }
            SqlParameter para = new SqlParameter("@ide", SqlDbType.Int);
            para.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(para);
            result = cmd.ExecuteNonQuery();
            cmd.Dispose();
            if (result > 0)
                pkValue = para.Value;
            else
                pkValue = null;
            return result > 0;
        }

        public bool Execute(SqlTransaction tran, string sql, params object[] objs)
        {
            int result = 0;
            SqlConnection conn = tran.Connection;
            SqlCommand cmd = conn.CreateCommand();
            cmd.Transaction = tran;
            cmd.CommandText = sql;
            if (objs != null && objs.Length > 0)
            {
                for (int i = 0; i < objs.Length; i++)
                {
                    cmd.Parameters.AddWithValue("@" + i, objs[i]);
                }
            }
            result = cmd.ExecuteNonQuery();
            cmd.Dispose();
            return result > 0;
        }

        public void close(SqlTransaction tran)
        {
            SqlConnection conn = tran.Connection;
            if (conn != null && conn.State == ConnectionState.Open)
                conn.Close();
        }


        public Map QueryMap(string sql, params object[] objs)
        {
            DataTable dt = QueryTable(sql, objs);
            if (dt != null && dt.Rows.Count > 0)
            {
                Map map = new Map();
                foreach (DataRow dr in dt.Rows)
                {
                    map.Add(dr[0].ToString(), dr[1]);
                }
                return map;
            }
            return null;
        }
    }
}
